#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


----------------------意向主题日宽表----------

insert  into hive.edu_dta.relationship_day
with temp as (select
                  ------维度字段----------
                  dcr.origin_type,       --线上线下
                  dcr.itcast_school_id,  -- 校区
                  dcr.school_name,
                  dcr.itcast_subject_id, -- 学科
                  dcr.subject_name,
                  dcr.origin_channel,    -- 来源渠道
                  dcr.area,              --地区
                  dcr.employee_id,
                  dcr.tdepart_id,
                  dcr.dep_id,            -- 各咨询中心,销售部门
                  dcr.dep_name,
                  dcr.clue_state,        --新老学员
                  dcr.create_date_time as date_line,--日期

                  ----------指标字段------------
                  dcr.id as relationship_id    ,       --意向客户id
                  ----------使用row_number进行去重
                  row_number() over(partition by dcr.id) as rela_rk

              from hive.edu_dws.customer_relationship dcr ---客户意向宽表
)
select
    --------------维度字段-----------------
     origin_type,       --线上线下
     itcast_school_id,  -- 校区
     school_name,
     itcast_subject_id, -- 学科
     subject_name,
     origin_channel,    -- 来源渠道
     area,              --地区
     tdepart_id,     -- 各咨询中心,销售部门
     dep_name,
     clue_state,        --新老学员
     DATE_FORMAT(date_line, '%Y') as year_date ,
     date_format(date_line,'%m') as month_date,
     date_format(date_line,'%d') as day_date,
     date_format(date_line,'%H') as hour_date,
--      relationship_id          ,       --意向客户id

    ------------------分组标记-------------------
    case
        when grouping(tdepart_id, dep_name) = 0
            then 'tdepart'
        when grouping(origin_channel) = 0
            then 'origin_channel'
        when grouping(itcast_school_id, school_name) = 0
            then 'itcast_school'
        when grouping(itcast_subject_id, subject_name) = 0
            then 'itcast_subject'
        when grouping(area) = 0
            then 'area'
        when grouping(origin_type) = 0
            then 'origin_type'
        when grouping(date_line) = 0
        then 'all'

        else 'others'
        end  as group_type,

    -------指标-------
    case
        when grouping(tdepart_id,dep_name) = 0
            then count(if(rela_rk = 1 and tdepart_id is not null, coalesce(relationship_id, 0), null))
        when grouping(origin_channel) = 0
            then count(if(rela_rk = 1 and origin_channel is not null, coalesce(relationship_id, 0), null))
        when grouping(itcast_school_id,school_name) = 0
            then count(if(rela_rk = 1 and itcast_school_id is not null, coalesce(relationship_id, 0), null))
        when grouping(itcast_subject_id,subject_name) = 0
            then count(if(rela_rk = 1 and itcast_subject_id is not null, coalesce(relationship_id, 0), null))
        when grouping(area) = 0
            then count(if(rela_rk= 1 and area is not null, coalesce(relationship_id, 0), null))
        when grouping(date_line) = 0
            then count(if(rela_rk = 1 and date_line is not null, coalesce(relationship_id, 0), null))
        when grouping(origin_type) = 0
            then count(if(rela_rk = 1 and origin_type is not null, coalesce(relationship_id, 0), null))

        else null
        end  as customer_relationship_amount

from temp
group by
    grouping sets ( (date_line, origin_type, clue_state),
                    (date_line, origin_type, clue_state, area),
                    (date_line, origin_type, clue_state, itcast_subject_id,subject_name),
                    (date_line, origin_type, clue_state, itcast_school_id,school_name),
                    (date_line, origin_type, clue_state, origin_channel),
                    (date_line, origin_type, clue_state, tdepart_id,dep_name)
    ) ;


----------------------线索主题日宽表----------

insert into hive.edu_dta.clue_day
with temp as (select
                  ------维度字段----------
                  dcc.origin_type,       --线上线下
                  dcc.clue_state,        --新老学员
                  dcc.create_date_time as date_line,--日期
                  ----------指标字段------------
                  dcc.id as clue_id  ,           --线索id
                  dcc.appeal_status,     --申诉状态
                  ----------使用row_number进行去重
                  row_number() over(partition by dcc.id) as clue_rk
                       from hive.edu_dws.customer_clue dcc ----客户线索宽表
)
select
    --------------维度字段-----------------
     origin_type,       --线上线下
     clue_state,        --新老学员
     DATE_FORMAT(date_line, '%Y') as year_date ,
     date_format(date_line,'%m') as month_date,
     date_format(date_line,'%d') as day_date,
     date_format(date_line,'%H') as hour_date,

    ------------------分组标记-------------------
    case
        when grouping(origin_type,clue_state) = 0
            then 'origin_type,clue_state'
        when grouping(date_line) = 0
            then 'all'

        else 'others'
        end  as group_type,

    -------指标-------
    case
        when grouping(origin_type,clue_state) = 0
            then count(if(clue_rk = 1 and origin_type is not null and clue_state is not null, coalesce(clue_id, 0), null))
        else null
        end  as clue_amount,

    case
        when grouping(origin_type,clue_state) = 0
            then count(if(clue_rk = 1 and origin_type is not null and clue_state is not null and appeal_status=2, coalesce(clue_id, 0), null))
        else null
        end  as val_clue_amount

from temp
group by
    grouping sets ( (date_line, origin_type, clue_state)

    ) ;




























